﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;

namespace FHJ.FHPI.PushNotifications.Library
{
    /// <summary>
    /// Helper class wrapping database functionality.
    /// </summary>
    public static class DbWrapper
    {
        /// <summary>
        /// The connection string used to connect to the database.
        /// </summary>
        private static String ConnectionString
        {
            get;
            set;
        }

        /// <summary>
        /// Initializes the database wrapper.
        /// </summary>
        /// <param name="connectionString">The connection string for the database.</param>
        public static void Initialize(String connectionString)
        {
            try
            {
                ConnectionString = connectionString;
            }
            catch (Exception)
            {                
                throw;
            }
        }
        
        /// <summary>
        /// Gets a set of messages for C2DM which haven't been processed yet.
        /// </summary>
        public static List<Types.C2DMProcessMessage> GetListOfOpenMessages_C2DM()
        {
            try
            {
                List<Types.C2DMProcessMessage> returnList = new List<Types.C2DMProcessMessage>();

                using (MySqlConnection dbConn = GetOpenConnection())
                {
                    StringBuilder sql = new StringBuilder();
                    sql.AppendLine("SELECT");
                    sql.AppendLine("C2DM_MESSAGE_PROCESSING.pushmessage_id,");
                    sql.AppendLine("C2DM_MESSAGE_PROCESSING.registration_id,");
                    sql.AppendLine("C2DM_MESSAGE_PROCESSING.completed,");
                    sql.AppendLine("C2DM_MESSAGE_PROCESSING.success,");
                    sql.AppendLine("C2DM_MESSAGE_PROCESSING.status_message,");
                    sql.AppendLine("PUSHMESSAGES.type,");
                    sql.AppendLine("PUSHMESSAGES.additionalinfo");
                    sql.AppendLine("FROM C2DM_MESSAGE_PROCESSING INNER JOIN PUSHMESSAGES ON (");
                    sql.AppendLine("C2DM_MESSAGE_PROCESSING.pushmessage_id  = PUSHMESSAGES.pushmessage_id)");
                    sql.AppendLine("WHERE COMPLETED = 0;");

                    DataTable tempTab = new DataTable();
                    using (MySqlDataAdapter dbAdap = new MySqlDataAdapter(sql.ToString(), dbConn))
                    {
                        dbAdap.Fill(tempTab);
                    }

                    foreach (DataRow row in tempTab.Rows)
                    {
                        Types.C2DMProcessMessage c2dmMessage = new Types.C2DMProcessMessage();
                        c2dmMessage.DeviceRegistrationId = (String)row["registration_id"];
                        c2dmMessage.Completed = Convert.ToBoolean(row["completed"]);
                        c2dmMessage.Success = (row.IsNull("success")) ? (Boolean?)null : Convert.ToBoolean(row["success"]);
                        c2dmMessage.StatusMessage = (row.IsNull("status_message")) ? null : (String)row["status_message"];

                        Types.PushMessage pushMessage = new Types.PushMessage();
                        pushMessage.Id = (String)row["pushmessage_id"];
                        pushMessage.Type = (String)row["type"];
                        pushMessage.AdditionalInfo = (row.IsNull("additionalinfo")) ? null : (String)row["additionalinfo"];

                        c2dmMessage.PushMessage = pushMessage;

                        returnList.Add(c2dmMessage);
                    }
                }

                return returnList;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// Updates the given item in database.
        /// </summary>
        /// <param name="item">The item to update in database.</param>
        public static void UpdateMessageItem_C2DM(Types.C2DMProcessMessage item)
        {
            try
            {
                if (item == null)
                    throw new ArgumentNullException("item");

                using (MySqlConnection dbConn = GetOpenConnection())
                {
                    using (MySqlCommand updCom = dbConn.CreateCommand())
                    {
                        StringBuilder sql = new StringBuilder();
                        sql.AppendLine("UPDATE C2DM_MESSAGE_PROCESSING SET");
                        sql.AppendLine("completed = ?completed,");
                        sql.AppendLine("success = ?success,");
                        sql.AppendLine("status_message = ?status_message");
                        sql.AppendLine("WHERE pushmessage_id = ?pushmessage_id AND registration_id = ?registration_id;");

                        updCom.CommandText = sql.ToString();

                        updCom.Parameters.Add("?completed", MySqlDbType.Bit).Value = (item.Completed) ? 1 : 0;

                        if (item.Success.HasValue) updCom.Parameters.Add("?success", MySqlDbType.Bit).Value = (item.Success.Value) ? 1 : 0;
                        else updCom.Parameters.Add("?success", MySqlDbType.Bit).Value = DBNull.Value;

                        if (!(item.StatusMessage == null)) updCom.Parameters.Add("?status_message", MySqlDbType.VarChar).Value = item.StatusMessage;
                        else updCom.Parameters.Add("?status_message", MySqlDbType.VarChar).Value = DBNull.Value;

                        updCom.Parameters.Add("?pushmessage_id", MySqlDbType.VarChar).Value = item.PushMessage.Id;

                        updCom.Parameters.Add("?registration_id", MySqlDbType.VarChar).Value = item.DeviceRegistrationId;

                        updCom.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception)
            {                
                throw;
            }
        }

        /// <summary>
        /// Deletes a registered C2DM client from database.
        /// </summary>
        /// <param name="deviceRegistrationId">The device registration id which should be deleted.</param>
        public static void DeleteC2DMClient(String deviceRegistrationId)
        {
            try
            {
                using (MySqlConnection dbConn = GetOpenConnection())
                {
                    using (MySqlCommand updCom = dbConn.CreateCommand())
                    {
                        StringBuilder sql = new StringBuilder();
                        sql.AppendLine("DELETE FROM C2DMCLIENTS WHERE registration_id = ?registration_id;");

                        updCom.CommandText = sql.ToString();
                        updCom.Parameters.Add("?registration_id", MySqlDbType.VarChar).Value = deviceRegistrationId;

                        updCom.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception)
            {
                
                throw;
            }
        }








        /// <summary>
        /// Gets an open connection to the database.
        /// </summary>
        /// <returns></returns>
        private static MySqlConnection GetOpenConnection()
        {
            try
            {
                MySqlConnection dbConn = new MySqlConnection(ConnectionString);
                dbConn.Open();
                return dbConn;
            }
            catch (Exception)
            {                
                throw;
            }
        }
    }
}
